<?php

require_once "myPDO.class.php";
require_once APP_ROOT . "/config/config.php";

/**
 * @author    1054229380@qq.com
 * @since    2017-7-3
 * @desc 数据库常用操作类
 * */
class db
{

    /**
     * 执行SQL语句并返回数组，所有数据输出均通出该入口
     * @param @sqlInfo
     * @param @typeId
     * @return array
     * */
    public static function getArr($sqlInfo, $typeId = '0')
    {
        global $myPDO;
        $getArrType = 0; //获取数组方式，0,数据库，1,文件缓存
        if ($typeId == 1) {
            $sql = $sqlInfo;
        } else {
            $sql = ($typeId == "0") ? self::buildSql($sqlInfo) : $sqlInfo;
        }
        if ($getArrType == "0") {
            $tmpArr = $myPDO->getAll($sql);
        }
        return $tmpArr;
    }

    public static function getArray($sql)
    {
        global $myPDO;
        $tmpArr = $myPDO->getAll($sql);
        return $tmpArr;
    }

    /**
     * 只取一条数据
     * @param @sqlArr 查询条件
     * @return array
     * */
    public static function getInfo($sqlArr)
    {
        $sqlArr['limitRows'] = '1';
        $backArr = self::getArr($sqlArr);
        if (isset($backArr[0])) {
            return $backArr[0];
        } else {
            return array();
        }
    }

    /**
     * 拼接SQL语句
     * @param @sqlArr 查询条件
     * @return sql
     * */
    public static function buildSql($sqlArr)
    {
        global $myPDO;
        $tableName = isset($sqlArr['tableName']) ? $sqlArr['tableName'] : "";
        $selectField = isset($sqlArr['selectField']) ? $sqlArr['selectField'] : "*";
        $whereCondition = isset($sqlArr['whereCondition']) ? $sqlArr['whereCondition'] : "";
        $orderBy = isset($sqlArr['orderBy']) ? $sqlArr['orderBy'] : "";
        $groupBy = isset($sqlArr['groupBy']) ? $sqlArr['groupBy'] : "";
        $offSet = isset($sqlArr['offSet']) ? $sqlArr['offSet'] : "0";
        $limitRows = isset($sqlArr['limitRows']) ? $sqlArr['limitRows'] : "";

        $dbType = $myPDO->_dbType;

        $sql = null;
        switch ($dbType) {
            case "mysql":
                $sql = "select {$selectField} from {$tableName} {$whereCondition} ";
                break;

            case "sqlsrv":
                $sql = "select top {$limitRows} {$selectField} from {$tableName} {$whereCondition} ";
                break;
        }

        if ($groupBy != "") {
            $sql .= "group by $groupBy";
        }

        if ($orderBy != "") {
            $sql .= " order by $orderBy";
        }

        if ($limitRows != "") {
            switch ($dbType) {
                case "mysql":
                    $sql .= " limit $offSet,$limitRows";
                    break;

                case "sqlsrv":
                    $sql .= "";
                    break;
            }
        }
        //echo $sql;
        return $sql;
    }

    /**
     * 返回需要查询的数据Id集
     * @param @sqlArr SQL查询条件
     * @return string
     * */
    public static function getIds($sqlArr)
    {
        $sql = self::buildSql($sqlArr);

        $dbArr = self::getArr($sql, 1);
        $_rows = count($dbArr);
        $spl = $idStr = null;
        for ($i = 0; $i < $_rows; $i++) {
            $id = $dbArr[$i]['id'];
            if ($i > 0) {
                $spl = ",";
            }
            $idStr = $idStr . $spl . $id;
        }
        return array(
            "sql" => $sql,
            "ids" => $idStr
        );
    }

    /**
     * 判断指定表中数据是否存在，该表必须含有id字段
     * @param @tableName 数据表名
     * @param @whereArr 查询条件
     * @return true,false
     * */
    public static function isExist($tableName, $whereArr = '')
    {
        //global $myPDO;
        //$myPDO->_debug=true;
        $whereStr = self::convertSearchToSql($whereArr);
        //return false;
        $backArr = self::getInfo(array(
            "tableName" => $tableName,
            "selectField" => "id",
            "whereCondition" => $whereStr
        ));
        if (isset($backArr['id'])) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 查询数据库总记录数
     * @param @tableName 数据表名
     * @param @whereStr 查询条件
     * @param @indexFiel 自定义索引字段
     * @return int
     * */
    public static function getCount($tableName, $whereStr = '', $indexFiel = '*')
    {
        $sql = "select count($indexFiel) as _tmpDbTotalRows from $tableName $whereStr";
        $_tmpCountArr = self::getArr($sql, 1);
        if (count($_tmpCountArr) > 1) {
            return count($_tmpCountArr);
        } else {
            return $_tmpCountArr[0]['_tmpDbTotalRows'];
        }
    }

    /**
     * 查询数据库指定字段总数量SUM
     * @param @tableName 数据表名
     * @param @whereStr 查询条件
     * @param @sumFiel 要统计数量的字段,多个字段以逗号隔开
     * @return int
     * */
    public static function getSum($tableName, $whereStr = '', $sumFiel)
    {
        if ($sumFiel == '') {
            exit("求和的字段不能为空！");
        }
        $sumSQLstr = null;
        $splStr = null;
        $fielArr = explode(",", $sumFiel);
        $sumFielArrNum = count($fielArr);
        for ($i = 0; $i < $sumFielArrNum; $i++) {
            if ($i > 0) {
                $splStr = ",";
            }
            $_tmpFiel = $fielArr[$i];
            $sumSQLstr = $sumSQLstr . $splStr . "sum($_tmpFiel) as _tmpDbTotalSum{$i}";
        }

        $sql = "select $sumSQLstr from $tableName $whereStr";
        $_tmpSumArr = self::getArr($sql, 1);
        if ($sumFielArrNum == 1) {
            return $_tmpSumArr[0]['_tmpDbTotalSum0'];
        }
        $_returnSumArr = array();
        for ($i = 0; $i < $sumFielArrNum; $i++) {
            $_returnSumArr[] = $_tmpSumArr[0]['_tmpDbTotalSum' . $i];
        }
        return $_returnSumArr;
    }

    /**
     * 将数组插入指定表
     * @param @tableName 数据表名
     * @param @dbData 数据数组
     * @return true,false
     * */
    public static function insert($tableName, $dbData)
    {
        global $myPDO;
        $how = 0;
        $spl = $fielStr = $valueStr = null;
        foreach (array_keys($dbData) as $val) {
            $keyName = $val;
            $keyValue = $dbData[$keyName];
            if ($how != "0") {
                $spl = ",";
            }
            $fielStr .= $spl . $keyName;
            $valueStr .= $spl . "'" . $keyValue . "'";
            $how++;
        }
        $sql = "insert into {$tableName}($fielStr) values($valueStr)";
        $result = $myPDO->query($sql);
        $insertId = db::getInsertId();
        if ($result != FALSE) {
            return $insertId;
        } else {
            return false;
        }
    }

    /**
     * 将数组更新至表
     * @param @tableName 数据表名
     * @param @dbData 更新数据数组
     * @param @whereStr 更新条件
     * @return true,false
     * */
    public static function update($tableName, $dbData, $whereStr = '')
    {
        global $myPDO;
        $how = 0;
        $spl = $fielStr = $valueStr = $upstr = null;
        foreach (array_keys($dbData) as $val) {
            $keyName = $val;
            $keyValue = $dbData[$keyName];
            if ($how != "0") {
                $spl = ",";
            }
            $upstr .= "{$spl}{$keyName}='{$keyValue}'";
            $how++;
        }
        $sql = "update {$tableName} set {$upstr} $whereStr";
        $result = $myPDO->query($sql);
        if ($result != FALSE) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 执行SQL语句
     * @param sql
     * @return true,false
     * */
    public static function runsql($sql)
    {
        global $myPDO;
        $result = $myPDO->query($sql);
        if ($result != FALSE) {
            return true;
        } else {
            return false;
        }
    }

    /**
     * 执行SQL语句
     */
    public static function querySql($sql)
    {
        global $myPDO;
        $result = $myPDO->query($sql);
        $insertId = db::getInsertId();
        if ($result != FALSE) {
            return $insertId;
        } else {
            return false;
        }
    }

    /**
     * 返回受影响记录数
     * */
    public static function getAffectedRows()
    {
        global $myPDO;
        return $myPDO->affectedRows;
    }

    /**
     * 返回受影响记录数
     * */
    public static function getInsertId()
    {
        global $myPDO;
        return $myPDO->newInsertId;
    }

    /**
     * 执行SQL事务
     * @param sql数组
     * @return true,false
     * */
    public static function runTransAction($sql, $actionType = '')
    {
        global $myPDO;
        $result = $myPDO->queryTransAction($sql, $actionType);
        //echo $result;
        if ($result == FALSE) {
            return false;
        } else {
            return true;
        }
    }

    /**
     * 转换搜索条件为页面链接
     * */
    public static function convertSearchToLinkStr()
    {
        $paraArr = $_GET;
        $tempStr = null;
        foreach (array_keys($paraArr) as $key => $val) {
            $keyName = $val;
            $keyValue = $paraArr[$keyName];
            if (is_array($keyValue)) {
                $keyValue = serialize($keyValue);
                $keyValue = base64_encode($keyValue);
                if ($key == "0") {
                    $tempStr = "?";
                }
                $tempStr .= $keyName . '=' . $keyValue . '&';
            } else {
                $keyValue = urlencode($keyValue);
                if ($keyName != "page") {
                    if ($key == "0") {
                        $tempStr = "?";
                    }
                    $tempStr .= $keyName . "=" . $keyValue . "&";
                }
            }
        }
        if ($tempStr == "") {
            $tempStr = "?";
        }
        $tempStr .= "page=";
        return $tempStr;
    }

    /**
     * 转换搜索条件为sql
     * */
    public static function convertSearchToSql($whereArr)
    {
        $selNum = count($whereArr);
        if ($selNum == 0 || !$whereArr) {
            return "";
        }
        $tempSql = null;
        for ($i = 0; $i < $selNum; $i++) {
            $selStr = $whereArr[$i];
            if ($i == "0") {
                $tempSql .= " where $selStr";
            } else {
                $tempSql .= " and $selStr";
            }
        }
        return $tempSql;
    }

}

?>